package com.bx.weixin.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.common.excel.ExcelExport;
import org.common.excel.ExcelHeader;

import com.bx.weixin.service.UserService;
import com.bx.weixin.servlet.WeixinUtils;
import com.demo.common.model.Company;
import com.demo.common.model.Relation;
import com.demo.common.model.User;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.ehcache.CacheKit;


/**
 * 业务相关类
 * @author ruiyifeng
 * @version 2017-6-28
 */ 
public class UserController extends Controller{
	
	static UserService service = new UserService();
	
	/**
	 * 注册者首页
	 */
	public void index() throws Exception {
		String openId = getPara("openId");
		if(openId == null || openId.equals("")){
			HttpServletResponse response = this.getResponse();
			response.sendRedirect("https://open.weixin.qq.com/connect/oauth2/authorize?appid=wx435fdd961701f449&redirect_uri=http%3a%2f%2fbxzx66.cn%2fbx%2foAuth&response_type=code&scope=snsapi_userinfo&state=STATE#wechat_redirect");
		}else{
			User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=? and u.state='1'", openId);
			if(user != null){
				Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", user.getCompanyId());
				setAttr("company", company);
				List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", user.getWeixinCode());
				User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", openId);
				setAttr("size", size.get("size"));
				setAttr("user", user);
				setAttr("list", list);
		    	render("/bx/zhuce/promise.html");
			}else{
				HttpServletResponse response = this.getResponse();
				response.sendRedirect("https://open.weixin.qq.com/connect/oauth2/authorize?appid=wx435fdd961701f449&redirect_uri=http%3a%2f%2fbxzx66.cn%2fbx%2foAuth&response_type=code&scope=snsapi_userinfo&state=STATE#wechat_redirect");
			}
	    	
		}
	}
	
	/**
	 * 注册者承诺页
	 */
	public void promise() {
		String openId = getPara("openId");
		User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=? and u.state='1'", openId);
		Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", user.getCompanyId());
    	List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", openId);
    	User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", openId);
		setAttr("size", size.get("size"));
    	setAttr("user", user);
		setAttr("company", company);
		setAttr("list", list);
    	render("/bx/zhuce/promise.html");
	}
	
	/**
	 * 新增、修改客户
	 */
	public void save() throws Exception{
		@SuppressWarnings("unused")
		HttpServletRequest request = this.getRequest();
		User u = getModel(User.class);
		User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=?", u.getWeixinCode());
		Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", u.getCompanyId());
		if(user == null){
			String c = company.getCompanyName();
			u.setCompanyName(c);
			u.setCreateTime(new Date());
			u.save();
		}else{
			u.setLogo(user.getLogo());
			user.setCompanyId(u.getCompanyId());
			user.setNickName(u.getNickName());
			user.setTel(u.getTel());
			String c = company.getCompanyName();
			user.setCompanyName(c);
			user.setState(1);
			user.setCreateTime(new Date());
			user.update();
		}
		
		List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.id = r.upvote_id AND r.target_id =?", u.getWeixinCode());
		User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", u.getWeixinCode());
		setAttr("size", size.get("size"));
		setAttr("user", u);
		setAttr("company", company);
		setAttr("list", list);
		render("/bx/zhuce/promise.html");
	}
	
	/**
	 * 阅读者首页
	 */
	@SuppressWarnings("unused")
	public void friend() throws Exception{
		HttpServletRequest request = this.getRequest();
		String	openId = getPara("openId");
		String oldOpenId = null;
		if(oldOpenId == null){
			oldOpenId = getPara("oldOpenId");
			HttpServletResponse response = this.getResponse();
			response.sendRedirect("https://open.weixin.qq.com/connect/oauth2/authorize?appid=wx435fdd961701f449&redirect_uri=http%3a%2f%2fbxzx66.cn%2fbx%2foAuth%2ffriend%3foldOpenId%3d"+oldOpenId+"&response_type=code&scope=snsapi_userinfo&state=STATE#wechat_redirect");
		}else if(oldOpenId != null && openId != null){
			Object[] paras = {oldOpenId, openId};
            Relation relation = Relation.dao.findFirst("select r.id from bx_relation r where r.target_id=? and r.upvote_id= ?", paras);
            if(relation == null){
        		User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=?", oldOpenId);
        		Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", user.getCompanyId());
        		List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", oldOpenId);
        		User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", oldOpenId);
        		setAttr("size", size.get("size"));
        		setAttr("user", user);
        		setAttr("company", company);
        		setAttr("list", list);
        		//setAttr("size", list.size());
        		setAttr("oldOpenId", oldOpenId);
        		setAttr("openId", openId);
        		render("/bx/yuedu/promise.html");
            }else{
            	User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=?", oldOpenId);
        		Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", user.getCompanyId());
            	List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", oldOpenId);
            	User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", oldOpenId);
        		setAttr("size", size.get("size"));
            	setAttr("user", user);
        		setAttr("company", company);
        		setAttr("list", list);
        		setAttr("openId", openId);
            	render("/bx/yuedu/promised.html");
            }
		}
	}
	
	/**
	 * 为他点赞 
	 */
	public void upVote() throws Exception{
		String openId = getPara("openId");
		String oldOpenId = getPara("oldOpenId");
		Relation r = Relation.dao.findFirst("select r.id FROM bx_relation r WHERE r.target_id=? AND r.upvote_id=?",oldOpenId,openId);
		User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=?", oldOpenId);
		Company company = Company.dao.findFirst("select c.* from bx_company c where c.id=?", user.getCompanyId());
		if(r == null){
			Db.update("INSERT INTO bx_relation(target_id,upvote_id,create_time) VALUES(?,?,?)", oldOpenId, openId,new Date());
			if(user.getUpvote() == null){
				user.setUpvote(1);
			}else{
				user.setUpvote(user.getUpvote() +1);
			}
			user.update();
			if(company.getUpvote() == null){
				company.setUpvote(1);
			}else{
				company.setUpvote(company.getUpvote() + 1);
			}
			company.update();
		}
    	List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", oldOpenId);
    	User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", oldOpenId);
		setAttr("size", size.get("size"));
    	setAttr("user", user);
		setAttr("company", company);
		setAttr("list", list);
		//setAttr("size", list.size());
		setAttr("openId", openId);
    	render("/bx/yuedu/promised.html");
	}
	
	
	/**
	 * 调用为微信接口
	 */
	public void  api() throws Exception{
		String url = getPara("url");
		HttpServletRequest request = this.getRequest();
		Map<String,String> map = WeixinUtils.setShareConfig(request, url);
		setAttr("map",map);
		renderJson();
		
	}
	
	/**
	 * 阅读者承诺页
	 */
	public void promises(){
		String oldOpenId = getPara("oldOpenId");
		String openId = getPara("openId");
		Object[] paras = {oldOpenId, openId};
        Relation relation = Relation.dao.findFirst("select r.id from bx_relation r where r.target_id=? and r.upvote_id= ?", paras);
        if(relation == null){
    		User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=?", oldOpenId);
    		Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", user.getCompanyId());
    		List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", oldOpenId);
    		User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", oldOpenId);
    		setAttr("size", size.get("size"));
    		setAttr("user", user);
    		setAttr("company", company);
    		setAttr("list", list);
    		//setAttr("size", list.size());
    		setAttr("oldOpenId", oldOpenId);
    		setAttr("openId", openId);
    		render("/bx/yuedu/promise.html");
        }else{
        	User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=?", oldOpenId);
    		Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", user.getCompanyId());
        	List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", oldOpenId);
        	User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", oldOpenId);
    		setAttr("size", size.get("size"));
        	setAttr("user", user);
    		setAttr("company", company);
    		setAttr("list", list);
    		setAttr("openId", openId);
    		setAttr("oldOpenId", oldOpenId);
    		//setAttr("size", list.size());
        	render("/bx/yuedu/promised.html");
        }
	}
	
	/**
	 * 阅读者我也要承诺 
	 */
	public void  reginster() throws Exception{
		String openId = getPara("openId");
		User user = User.dao.findFirst("select u.* from bx_user u where u.weixin_code=? and u.state='1'", openId);
		if(user == null){
        	Long count = service.count();
    		setAttr("count",count);
    		//百万
      		Long bw = count / 1000000 % 10;
      		if(bw == 0){
      			setAttr("bw", "a");
      		}else{
      			setAttr("bw", bw);
      		}
      		//十万
      		Long sw = count / 100000 % 10;
      		if(sw == 0 && bw==0){
      			setAttr("sw", "a");
      		}else{
      			setAttr("sw", sw);
      		}
      		//万
      		Long w = count / 10000 % 10;
      		if(w == 0 && sw == 0 && bw==0){
      			setAttr("w", "a");
      		}else{
      			setAttr("w", w);
      		}
      		//千
      		Long q = count / 1000 % 10;
      		if(q == 0 && w == 0 && sw == 0 && bw==0){
      			setAttr("q", "a");
      		}else{
      			setAttr("q", q);
      		}
      		//百
      		Long b = count / 100 % 10;
      		if(b == 0 && q == 0 && w == 0 && sw == 0 && bw==0){
      			setAttr("b", "a");
      		}else{
      			setAttr("b", b);
      		}
      		//十
      		Long s = count / 10 % 10;
      		if(s == 0 && b == 0 && q == 0 && w == 0 && sw == 0 && bw==0){
      			setAttr("s", "a");
      		}else{
      			setAttr("s", s);
      		}
      		//个
      		Long g = count % 10;
      		setAttr("g", g);
    		List<Company> company = service.companyList();
    		setAttr("user", user);
    		setAttr("company", company);
    		setAttr("openId", openId);
    		render("/bx/zhuce/reginster.html");
        }else{
    		Company company = Company.dao.findFirst("select c.company_name from bx_company c where c.id=?", user.getCompanyId());
        	List<User> list = User.dao.find("SELECT u.logo FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =? ORDER BY r.create_time DESC LIMIT 18", openId);
        	User size = User.dao.findFirst("SELECT COUNT(u.id) as size FROM bx_user u, bx_relation r WHERE u.weixin_code = r.upvote_id AND r.target_id =?", openId);
    		setAttr("size", size.get("size"));
        	setAttr("user", user);
    		setAttr("company", company);
    		setAttr("list", list);
    		setAttr("openId", openId);
    		//setAttr("size", list.size());
        	render("/bx/zhuce/promise.html");
        }
		
	}
	/**
	 * 排行榜
	 */
	public void rank(){
		List<User> list = User.dao.find("SELECT u.logo,u.company_name,u.nick_name,u.upvote FROM bx_user u WHERE u.upvote>0 ORDER BY u.upvote DESC LIMIT 50");
		List<Company> cList = Company.dao.find("SELECT c.company_name,c.upvote FROM bx_company c WHERE c.upvote > 0 ORDER BY c.upvote DESC LIMIT 20");
		setAttr("list", list);
		setAttr("cList", cList);
		String openId = getPara("openId");
		setAttr("openId", openId);
		render("/bx/zhuce/rank.html");
	}
	
	/**
	 * 后台客户资料导出excel 
	 */
	@SuppressWarnings({ "deprecation", "resource" })
	public void export() throws Exception{
		HttpServletResponse response = this.getResponse();
		HttpServletRequest request = this.getRequest();
		List<User> list = User.dao.find("select u.* from bx_user u where u.state='1' order by u.create_time desc");
		ServletOutputStream stream = response.getOutputStream();
		HSSFWorkbook workbook = new HSSFWorkbook();
			// 生成一个字体
			HSSFFont font = workbook.createFont();
			font.setColor(HSSFColor.BLACK.index);
			font.setFontHeightInPoints((short) 11);

			// 生成一个样式
			HSSFCellStyle style = workbook.createCellStyle();
			style.setFillForegroundColor(HSSFColor.WHITE.index);
			style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			style.setFont(font);
			style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			style.setBorderRight(HSSFCellStyle.BORDER_THIN);
			style.setBorderTop(HSSFCellStyle.BORDER_THIN);
			// 生成一个表格
			HSSFSheet sheet = workbook.createSheet();
			sheet.setDefaultColumnWidth((short) 20);// 单元格宽度
			//sheet.setDefaultRowHeightInPoints(25);
			// 设置表格默认列宽度为15个字节
			//表头
			String[] headers = new String[] { "id", "姓名", "电话", "品牌", "注册时间"};
			HSSFRow row1 = sheet.createRow(0);
			row1.setHeightInPoints(25);
			for (int i = 0; i < headers.length; i++) {
				HSSFCell cell = row1.createCell(i);
				cell.setCellValue(headers[i]);
				cell.setCellStyle(style);
			}
			int index = 1;
			for (User u: list) {
				HSSFRow row = sheet.createRow(index);
				row.setHeightInPoints(25);
				HSSFCell cell0 = row.createCell(0);
				cell0.setCellValue(u.getId().toString());
				cell0.setCellStyle(style);
				HSSFCell cell1 = row.createCell(1);
				cell1.setCellValue(u.getNickName().toString());
				cell1.setCellStyle(style);
				HSSFCell cell2 = row.createCell(2);
				cell2.setCellValue(u.getTel().toString());
				cell2.setCellStyle(style);
				HSSFCell cell3 = row.createCell(3); 
				cell3.setCellValue(u.getCompanyName().toString());
				cell3.setCellStyle(style);
				HSSFCell cell4 = row.createCell(4); 
				cell4.setCellValue(u.getCreateTime().toString());
				cell4.setCellStyle(style);
				index++;
			}
			
			String filename ="客户数据.xls";
			// 判断是哪种浏览器发送的请求,对文件名进行响应的编码设置
			if (request.getHeader("User-Agent").toLowerCase().indexOf("firefox") > 0) {
				filename = new String(filename.getBytes("UTF-8"), "ISO8859-1");
			}
			// IE
			else if (request.getHeader("User-Agent").toUpperCase().indexOf("MSIE") > 0) {
				filename = URLEncoder.encode(filename, "UTF-8");
			} else {
				filename = URLEncoder.encode(filename, "UTF-8");
			}
			response.setHeader("Content-Disposition", "attachment;filename=\""
					+ filename + "\"");
			response.setContentType("application/vnd.ms-excel;charset=utf-8");
		try {
			workbook.write(stream);
		} catch (IOException e) {
			System.out.println("导出失败");
			stream.close();
		}
	}
}
